--
-- Beispieltabellen fuer die Pruefungsdatenbank
--
-- Susanne Busse,  April 2012 

drop sequence S_UNIPfach;
drop table UNIErgebnis;
drop table UNIStudent;
drop table UNIPfach;
drop table UNIFakultaet;


create table UNIFakultaet (
  FakNr   Numeric(2,0)  not null
 ,FakName Varchar(40)   not null
 ,Dekan   Varchar(20)   null
 ,PRIMARY KEY (FakNr)
);


create table UNIPfach (
  PNr     integer  not null 
 ,Fach    Varchar(10)   not null
 ,FakNr   Numeric(2,0)  not null
 ,PRIMARY KEY(PNr)
 ,FOREIGN KEY(FakNr) REFERENCES UNIFakultaet
                      -- on update cascade 
);
create sequence S_UNIPfach;
create or replace trigger UNIInsertPfach
before insert on UNIPfach
for each row
begin
  if (:new.PNr is null) then
    select S_UNIPfach.nextval into :new.PNr from Dual;
  end if;
end;
/


create table UNIStudent (
  MatNr   Numeric(7,0)  not null
 ,Name    Varchar(20)   not null
 ,Vorname Varchar(20)   not null
 ,Geb     Date          not null
 ,FakNr   Numeric(2,0)  not null
 ,PRIMARY KEY (MatNr)
 ,FOREIGN KEY (FakNr) REFERENCES UNIFakultaet(FakNr)
 --                       on update cascade
);


create table UNIErgebnis (
  PNr     integer      not null 
 ,MatNr   Numeric(7,0) not null 
 ,Pruefer Varchar(20)  not null 
 ,Termin  DATE         null	
 ,Note    Numeric(2,1) null     check (Note <= 5.0)	
 ,PRIMARY KEY (PNr, MatNr)
 ,FOREIGN KEY (PNr  ) REFERENCES UNIPfach(PNr)
  --                      on update cascade 
 ,FOREIGN KEY (MatNr) REFERENCES UNIStudent(MatNr)
   --                     on update cascade on delete cascade
);



-- --------------------------- Beispieldaten ----------------------------- --

-- Fakultaetsdaten zu UNIFakultaet(FakNr, FakName, Dekan)
INSERT INTO UNIFakultaet VALUES (4, 'Elektrotechnik und Informatik', 'Hommel');
INSERT INTO UNIFakultaet VALUES (2, 'Mathematik und Naturwissenschaften', 'Thomsen');
INSERT INTO UNIFakultaet VALUES (8, 'Wirtschaft und Management', 'Hunscha');
COMMIT;

-- Daten der Pruefungsfaecher Pfach(PNr, Fach, FakNr)   
-- Die PNr wird automatisch eingetragen.
INSERT INTO UNIPfach(Fach,FakNr) VALUES ('Info A', 4);
INSERT INTO UNIPfach(Fach,FakNr) VALUES ('TEGI'  , 4);
INSERT INTO UNIPfach(Fach,FakNr) VALUES ('PDV'   , 4);
INSERT INTO UNIPfach(Fach,FakNr) VALUES ('Mafi'  , 2);
INSERT INTO UNIPfach(Fach,FakNr) VALUES ('GIM'   , 4);
INSERT INTO UNIPfach(Fach,FakNr) VALUES ('DBS'   , 4);
COMMIT;

-- Daten der Studenten, d.h. zu UNIStudent(MatNr, Name, Vorname, Geb, FakNr)
INSERT INTO UNIStudent VALUES (210001, 'Huber'  , 'Hans'     , '01-01-1975', 4 );
INSERT INTO UNIStudent VALUES (210002, 'Meier'  , 'Mirko'    , '03-05-1978', 2 );
INSERT INTO UNIStudent VALUES (210003, 'Bauer'  , 'Bertram'  , '13-05-1977', 4 );
INSERT INTO UNIStudent VALUES (210004, 'Paulsen', 'Peter'    , '05-09-1982', 8 );
INSERT INTO UNIStudent VALUES (210005, 'Wichtig', 'Wolfram'  , '13-01-1984', 8 );
INSERT INTO UNIStudent VALUES (210006, 'Gans'   , 'Gertrud'  , '25-04-1980', 4 );
INSERT INTO UNIStudent VALUES (210007, 'Fersen' , 'Frederick', '12-01-1980', 4 );
COMMIT;


-- Pruefungsergebnisse zu UNIErgebnis(MatNr, PNr, Pruefer, Termin, Note)
INSERT INTO UNIErgebnis VALUES ( 1, 210001, 'Pepper'  , '21-06-2006', 2.7);
INSERT INTO UNIErgebnis VALUES ( 6, 210002, 'Weber'   , '27-07-2003', 2.0);
INSERT INTO UNIErgebnis VALUES ( 2, 210001, 'Ehrig'   , '18-07-2003', 1.7);
INSERT INTO UNIErgebnis VALUES ( 5, 210002, 'Kutsche' , '15-07-2003', 1.7);
INSERT INTO UNIErgebnis VALUES ( 3, 210001, 'Hommel'  , '22-07-2003', 3.0);
INSERT INTO UNIErgebnis VALUES ( 6, 210001, 'Busse'   , '15-07-2003', 1.3);
INSERT INTO UNIErgebnis VALUES ( 5, 210003, 'Kutsche' , '15-07-2003', 1.7);
INSERT INTO UNIErgebnis VALUES ( 3, 210003, 'Hommel'  , '22-07-2003', 3.0);
INSERT INTO UNIErgebnis VALUES ( 1, 210004, 'Pepper'  , '21-03-2006', 2.7);
INSERT INTO UNIErgebnis VALUES ( 3, 210004, 'Hommel'  , NULL, NULL);
INSERT INTO UNIErgebnis VALUES ( 6, 210005, 'Busse'   , '15-07-2003', 1.3);
INSERT INTO UNIErgebnis VALUES ( 5, 210005, 'Weber'   , '27-07-2003', 2.0);
INSERT INTO UNIErgebnis VALUES ( 1, 210006, 'Pepper'  , '21-03-2006', 2.7);
INSERT INTO UNIErgebnis VALUES ( 2, 210006, 'Ehrig'   , '18-07-2003', 1.7);
INSERT INTO UNIErgebnis VALUES ( 5, 210007, 'Weber'   , '27-07-2003', 2.0);
INSERT INTO UNIErgebnis VALUES ( 6, 210007, 'Busse'   , '03-08-2003', NULL);
COMMIT;
